package com.xuanzheng.company.dao.impl;

import com.xuanzheng.company.dao.EmployeePerformanceDao;
import com.xuanzheng.company.entity.EmployeePerformance;
import com.xuanzheng.company.util.DBUtil;
import com.xuanzheng.company.util.IdGenerator;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class EmployeePerformanceDaoImpl implements EmployeePerformanceDao {

    @Override
    public int save(EmployeePerformance performance) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO employee_performance (id, company_id, user_id, employee_number, employee_name, " +
                    "position, year, month, attendance_hours, avg_task_score, performance) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);
            
            // 生成唯一ID
            if (performance.getId() == null || performance.getId().isEmpty()) {
                String id = IdGenerator.generateId();
                performance.setId(id);
            }
            
            pstmt.setString(1, performance.getId());
            pstmt.setString(2, performance.getCompanyId());
            pstmt.setString(3, performance.getUserId());
            pstmt.setString(4, performance.getEmployeeNumber());
            pstmt.setString(5, performance.getEmployeeName());
            pstmt.setString(6, performance.getPosition());
            pstmt.setInt(7, performance.getYear());
            pstmt.setInt(8, performance.getMonth());
            pstmt.setDouble(9, performance.getAttendanceHours());
            pstmt.setDouble(10, performance.getAvgTaskScore());
            pstmt.setDouble(11, performance.getPerformance());
            
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        
        return result;
    }

    @Override
    public int update(EmployeePerformance performance) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE employee_performance SET company_id = ?, user_id = ?, employee_number = ?, " +
                    "employee_name = ?, position = ?, year = ?, month = ?, attendance_hours = ?, " +
                    "avg_task_score = ?, performance = ? WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            
            pstmt.setString(1, performance.getCompanyId());
            pstmt.setString(2, performance.getUserId());
            pstmt.setString(3, performance.getEmployeeNumber());
            pstmt.setString(4, performance.getEmployeeName());
            pstmt.setString(5, performance.getPosition());
            pstmt.setInt(6, performance.getYear());
            pstmt.setInt(7, performance.getMonth());
            pstmt.setDouble(8, performance.getAttendanceHours());
            pstmt.setDouble(9, performance.getAvgTaskScore());
            pstmt.setDouble(10, performance.getPerformance());
            pstmt.setString(11, performance.getId());
            
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }
        
        return result;
    }

    @Override
    public EmployeePerformance getById(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        EmployeePerformance performance = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM employee_performance WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                performance = mapResultSetToPerformance(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return performance;
    }

    @Override
    public EmployeePerformance getByUserIdAndYearMonth(String userId, int year, int month) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        EmployeePerformance performance = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM employee_performance WHERE user_id = ? AND year = ? AND month = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            pstmt.setInt(2, year);
            pstmt.setInt(3, month);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                performance = mapResultSetToPerformance(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return performance;
    }

    @Override
    public List<EmployeePerformance> getByCompanyIdAndYearMonth(String companyId, int year, int month) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<EmployeePerformance> performances = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM employee_performance WHERE company_id = ? AND year = ? AND month = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, companyId);
            pstmt.setInt(2, year);
            pstmt.setInt(3, month);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                EmployeePerformance performance = mapResultSetToPerformance(rs);
                performances.add(performance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return performances;
    }

    @Override
    public List<EmployeePerformance> getByUserId(String userId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<EmployeePerformance> performances = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM employee_performance WHERE user_id = ? ORDER BY year DESC, month DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userId);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                EmployeePerformance performance = mapResultSetToPerformance(rs);
                performances.add(performance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }

        return performances;
    }

    @Override
    public int delete(String id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int result = 0;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM employee_performance WHERE id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            result = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, null);
        }

        return result;
    }

    private EmployeePerformance mapResultSetToPerformance(ResultSet rs) throws SQLException {
        EmployeePerformance performance = new EmployeePerformance();
        performance.setId(rs.getString("id"));
        performance.setCompanyId(rs.getString("company_id"));
        performance.setUserId(rs.getString("user_id"));
        performance.setEmployeeNumber(rs.getString("employee_number"));
        performance.setEmployeeName(rs.getString("employee_name"));
        performance.setPosition(rs.getString("position"));
        performance.setYear(rs.getInt("year"));
        performance.setMonth(rs.getInt("month"));
        performance.setAttendanceHours(rs.getDouble("attendance_hours"));
        performance.setAvgTaskScore(rs.getDouble("avg_task_score"));
        performance.setPerformance(rs.getDouble("performance"));
        performance.setCreateTime(rs.getTimestamp("create_time"));
        performance.setUpdateTime(rs.getTimestamp("update_time"));
        return performance;
    }
} 